package com.itboye.ihomebank.db;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.itboye.ihomebank.bean.KeyBean;

import java.util.ArrayList;
import java.util.List;

/**
 * 我的钥匙列表
 *
 */
public class MyKeyService {
	
	DBOpenHelper dbOpenHelper;
	SQLiteDatabase db;
	
	public MyKeyService(Context context){  
        this.dbOpenHelper=new DBOpenHelper(context);  
    } 
	
	/**
	 * 添加
	 * @param key
	 */
    public void add(KeyBean key){  
        SQLiteDatabase db=dbOpenHelper.getWritableDatabase();  
        db.execSQL("insert into mykey(lock_id,key_id,start,end,create_time,update_time,status,type,power,lock_alias,house_no,user_type,lock_mac,push,key_status,is_new,is_new) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
        		new Object[]{key.getLockId(),key.getKeyId(),key.getStart(),key.getEnd(),key.getCreateTime(),key.getUpdateTime(),key.getStatus(),key.getType(),key.getPower(),key.getLockAlilas(),key.getHouseNo(),key.getUserType(),key.getLockMac(),key.getPush(),key.getKeyStatus(),key.getIsNew()});
    }
	
    /**
     * 删除  
     * @param id
     */
    public void delete(String id){  
        SQLiteDatabase db=dbOpenHelper.getWritableDatabase();  
        db.execSQL("delete from mykey where id=?",new String[]{id});  
    }
    /**
     * 删除  
     * @param keyid
     */
    public void deletekey(String keyid){  
        SQLiteDatabase db=dbOpenHelper.getWritableDatabase();  
        db.execSQL("delete from mykey where key_id=?",new String[]{keyid});  
    }
    /**
     * 修改  
     * @param key
     */
    public void update(KeyBean key){  
        SQLiteDatabase db=dbOpenHelper.getWritableDatabase();  
        db.execSQL("update mykey set lock_id=?,key_id=?,start=?,end=?,create_time=?,update_time=?,status=?,type=?,power=?,lock_alias=?,house_no=?,user_type=?,lock_mac=?,push=?,key_status=?,is_new=? where id=?",new Object[]{key.getLockId(),key.getKeyId(),key.getStart(),key.getEnd(),key.getCreateTime(),key.getUpdateTime(),key.getStatus(),key.getType(),key.getPower(),key.getLockAlilas(),key.getHouseNo(),key.getUserType(),key.getLockMac(),key.getPush(),key.getKeyStatus(),key.getIsNew()});
          
    } 
    /**
     * 查询  
     * @param id
     * @return
     */
    public KeyBean find(String id){  
    	KeyBean key=new KeyBean();
        SQLiteDatabase db=dbOpenHelper.getReadableDatabase();  
        Cursor cursor=db.rawQuery("select * from mykey where id=?", new String[]{id}); 
        if(cursor.moveToFirst()){  
        	String userid=cursor.getString(cursor.getColumnIndex("id"));  
            String lockId=cursor.getString(cursor.getColumnIndex("lock_id"));  
            String keyId=cursor.getString(cursor.getColumnIndex("key_id"));  
            String start=cursor.getString(cursor.getColumnIndex("start"));  
            String end=cursor.getString(cursor.getColumnIndex("end"));  
            String create_time=cursor.getString(cursor.getColumnIndex("create_time"));  
            String update_time=cursor.getString(cursor.getColumnIndex("update_time"));  
            String status=cursor.getString(cursor.getColumnIndex("status"));  
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String power=cursor.getString(cursor.getColumnIndex("power"));
            String lock_alias=cursor.getString(cursor.getColumnIndex("lock_alias")); 
            String house_no=cursor.getString(cursor.getColumnIndex("house_no"));  
            String user_type=cursor.getString(cursor.getColumnIndex("user_type"));  
            String lock_mac=cursor.getString(cursor.getColumnIndex("lock_mac"));  
            String push=cursor.getString(cursor.getColumnIndex("push"));  
            String keyStatus=cursor.getString(cursor.getColumnIndex("key_status")); 
            String is_new=cursor.getString(cursor.getColumnIndex("is_new"));

            key.setId(userid);
            key.setLockId(lockId);
            key.setKeyId(keyId);
            key.setStart(start);
            key.setEnd(end);
            key.setCreateTime(create_time);
            key.setUpdateTime(update_time);
            key.setStatus(status);
            key.setType(type);
            key.setPower(power);
            key.setLockAlilas(lock_alias);
            key.setHouseNo(house_no);
            key.setUserType(user_type);
            key.setLockMac(lock_mac);
            key.setPush(push);
            key.setKeyStatus(keyStatus);
            key.setIsNew(is_new);
        }
        return key;
    }
	
    /**
     * 通过锁别名查询数据  
     * @param lock_alias
     * @return
     */
    public KeyBean aliasQuery(String lock_alias){  
    	KeyBean key=new KeyBean();
        SQLiteDatabase db=dbOpenHelper.getReadableDatabase();  
        Cursor cursor=db.rawQuery("select * from mykey where lock_alias=?", new String[]{lock_alias}); 
        if(cursor.moveToFirst()){  
        	String userid=cursor.getString(cursor.getColumnIndex("id"));  
            String lockId=cursor.getString(cursor.getColumnIndex("lock_id"));  
            String keyId=cursor.getString(cursor.getColumnIndex("key_id"));  
            String start=cursor.getString(cursor.getColumnIndex("start"));  
            String end=cursor.getString(cursor.getColumnIndex("end"));  
            String create_time=cursor.getString(cursor.getColumnIndex("create_time"));  
            String update_time=cursor.getString(cursor.getColumnIndex("update_time"));  
            String status=cursor.getString(cursor.getColumnIndex("status"));  
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String power=cursor.getString(cursor.getColumnIndex("power"));
            String lockAlias=cursor.getString(cursor.getColumnIndex("lock_alias")); 
            String house_no=cursor.getString(cursor.getColumnIndex("house_no"));  
            String user_type=cursor.getString(cursor.getColumnIndex("user_type"));  
            String lock_mac=cursor.getString(cursor.getColumnIndex("lock_mac"));  
            String push=cursor.getString(cursor.getColumnIndex("push"));  
            String keyStatus=cursor.getString(cursor.getColumnIndex("key_status")); 
            String is_new=cursor.getString(cursor.getColumnIndex("is_new"));

            key.setId(userid);
            key.setLockId(lockId);
            key.setKeyId(keyId);
            key.setStart(start);
            key.setEnd(end);
            key.setCreateTime(create_time);
            key.setUpdateTime(update_time);
            key.setStatus(status);
            key.setType(type);
            key.setPower(power);
            key.setLockAlilas(lockAlias);
            key.setHouseNo(house_no);
            key.setUserType(user_type);
            key.setLockMac(lock_mac);
            key.setPush(push);
            key.setKeyStatus(keyStatus);
            key.setIsNew(is_new);
        }
        return key;
    }
	
    /**
     * 查找全部数据 
     * @return
     */
    public List<KeyBean> getAllUserList(){
        List<KeyBean> keys=new ArrayList<KeyBean>();  
        SQLiteDatabase db=dbOpenHelper.getReadableDatabase();  
        Cursor cursor = db.rawQuery("select * from mykey", null);
        while (cursor.moveToNext()) {
        	String userid=cursor.getString(cursor.getColumnIndex("id"));  
            String lockId=cursor.getString(cursor.getColumnIndex("lock_id"));  
            String keyId=cursor.getString(cursor.getColumnIndex("key_id"));  
            String start=cursor.getString(cursor.getColumnIndex("start"));  
            String end=cursor.getString(cursor.getColumnIndex("end"));  
            String create_time=cursor.getString(cursor.getColumnIndex("create_time"));  
            String update_time=cursor.getString(cursor.getColumnIndex("update_time"));  
            String status=cursor.getString(cursor.getColumnIndex("status"));  
            String type=cursor.getString(cursor.getColumnIndex("type"));
            String power=cursor.getString(cursor.getColumnIndex("power"));
            String lockAlias=cursor.getString(cursor.getColumnIndex("lock_alias")); 
            String house_no=cursor.getString(cursor.getColumnIndex("house_no"));  
            String user_type=cursor.getString(cursor.getColumnIndex("user_type"));  
            String lock_mac=cursor.getString(cursor.getColumnIndex("lock_mac"));  
            String push=cursor.getString(cursor.getColumnIndex("push"));  
            String keyStatus=cursor.getString(cursor.getColumnIndex("key_status")); 
            String is_new=cursor.getString(cursor.getColumnIndex("is_new"));

            KeyBean key=new KeyBean(); 
            key.setId(userid);
            key.setLockId(lockId);
            key.setKeyId(keyId);
            key.setStart(start);
            key.setEnd(end);
            key.setCreateTime(create_time);
            key.setUpdateTime(update_time);
            key.setStatus(status);
            key.setType(type);
            key.setPower(power);
            key.setLockAlilas(lockAlias);
            key.setHouseNo(house_no);
            key.setUserType(user_type);
            key.setLockMac(lock_mac);
            key.setPush(push);
            key.setKeyStatus(keyStatus);
            key.setIsNew(is_new);
            keys.add(key);
        }
        return keys;  
    } 
	
    /**
	 * 清空数据
	 */
	public void deleteData() {
		db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from mykey");
		db.close();
	}

}
